Module 6 of 16 · 📖 4 min read · ⏱ 30 min total

FI-AE 06 SQL — DDL, DML, Joins und Aggregate (EN)

Table of contents (5 sections)

Concepts and Background
Practical Steps
Common Pitfalls
Further Resources
Knowledge Check

FI-AE 06 SQL — DDL, DML, Joins and Aggregate

In this module, you will learn the fundamental concepts of database manipulation and querying with SQL. You will structure tables, insert, modify, and delete data, and create complex queries with joins, aggregate functions, and subqueries. The focus is on practical work with relational databases and understanding transactions.

Concepts and Background

DDL (Data Definition Language): Commands for defining and modifying the database structure, including CREATE, ALTER, and DROP.
DML (Data Manipulation Language): Commands for manipulating data in tables, mainly INSERT, UPDATE, DELETE, and SELECT.
JOIN: An operation for combining rows from two or more tables based on related columns.
Aggregate functions: Functions that summarize multiple values into a single value, such as COUNT, SUM, AVG, MIN, and MAX.
Transactions: A group of database operations that are treated as a single unit, with ACID properties (Atomicity, Consistency, Isolation, Durability).

Practical Steps

Create a new table with primary key and foreign key relationships. This establishes the basic structure for your database.

CREATE TABLE customers (
      customer_id INT PRIMARY KEY,
      name VARCHAR(100),
      email VARCHAR(100)
    );

    CREATE TABLE orders (
      order_id INT PRIMARY KEY,
      customer_id INT,
      date DATE,
      amount DECIMAL(10,2),
      FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
    );

Insert data into the tables using INSERT commands. This initializes your database with sample data.

INSERT INTO customers (customer_id, name, email) VALUES 
      (1, 'Max Mustermann', '[email protected]'),
      (2, 'Erika Mustermann', '[email protected]');

    INSERT INTO orders (order_id, customer_id, date, amount) VALUES
      (101, 1, '2023-01-15', 99.99),
      (102, 2, '2023-01-16', 149.50);

Use SELECT with JOIN to combine data from multiple tables. This enables complex queries across relational data.

SELECT c.name, o.date, o.amount
    FROM customers c
    JOIN orders o ON c.customer_id = o.customer_id;

Apply GROUP BY and HAVING to group and filter results. This is useful for aggregated analyses.

SELECT c.customer_id, c.name, COUNT(o.order_id) as number_of_orders
    FROM customers c
    LEFT JOIN orders o ON c.customer_id = o.customer_id
    GROUP BY c.customer_id, c.name
    HAVING COUNT(o.order_id) > 0;

Implement a transaction with BEGIN, COMMIT, and ROLLBACK to ensure data consistency.

BEGIN;
    UPDATE customers SET email = '[email protected]' WHERE customer_id = 1;
    INSERT INTO orders (order_id, customer_id, date, amount) 
    VALUES (103, 1, '2023-01-17', 79.99);
    COMMIT;

Common Pitfalls

Further Resources

Knowledge Check

Four questions for self-assessment. Click on each question to see the correct answer and explanation.

Which SQL command belongs to DDL (Data Definition Language) and is used to create a new table in a database?

A) INSERT INTO
B) CREATE TABLE
C) SELECT * FROM
D) UPDATE table SET

Correct Answer: B. CREATE TABLE is a DDL command for creating table structures. INSERT INTO, SELECT, and UPDATE are DML commands for data manipulation.

Which JOIN type would return all rows from the left table and the matching rows from the right table?

A) INNER JOIN
B) RIGHT JOIN
C) LEFT JOIN
D) FULL OUTER JOIN

Correct Answer: C. LEFT JOIN returns all rows from the left table and matching rows from the right table. INNER JOIN returns only matching rows, RIGHT JOIN is the opposite of LEFT JOIN, and FULL OUTER JOIN returns all rows from both tables.

Which SQL function would count the number of rows in a table, excluding NULL values?

A) SUM()
B) AVG()
C) COUNT(*)
D) COUNT(column)

Correct Answer: D. COUNT(column) counts only the rows where the specified column is not NULL. COUNT(*) counts all rows regardless of NULL values, SUM() adds values, and AVG() calculates the average.

What is the main purpose of database transactions?

A) To improve query performance
B) To ensure that a group of operations is treated as an atomic unit
C) To reduce storage space in the database
D) To increase the security of user data

Correct Answer: B. Transactions ensure that a group of operations is treated as an atomic unit (ACID properties). They do not directly improve performance, do not necessarily reduce storage space, and do not primarily increase security.